//{$DEFINE MSSQL}
unit DataLayer;

interface

{$IFDEF IOS}
uses
  System.SysUtils, System.Classes, Data.FMTBcd, Data.DB,
  Data.SqlExpr, Data.DbxSqlite,System.IOUtils;
{$ELSE}
uses
  System.SysUtils, System.Classes, Data.FMTBcd, Data.DB,
  Data.SqlExpr, Data.DbxMSSQL, Data.DbxSqlite;
{$ENDIF}


type
  TDataModule1 = class(TDataModule)
  SQLQuery: TSQLQuery;
  AlexConn : TSQLConnection;
  private
    { Private declarations }
       function GetConnection: TSQLConnection;

    function OpenQuery(sql:string) : TSQLQuery;
  public
    { Public declarations }

    function GetMenuItems: TSQLQuery;
    function GetCategoryItems: TSQLQuery;
    function GetTablePages: TSQLQuery;
    function GetTableLayout : TSQLQuery;
    function GetProfile: TSQLQuery;
    function GetMenuInstructionLink: TSQLQuery;
    function GetVIPTable: TSQLQuery;
  end;

var
  DataModule1: TDataModule1;

implementation

{%CLASSGROUP 'FMX.Controls.TControl'}


{$R *.dfm}
 function TDataModule1.GetMenuItems: TSQLQuery;
 var sql : string;
 begin
    SQLQuery.SQLConnection := GetConnection;
    sql := 'select itemcode,category,description1,' +
           'price,multiple,subdescription,price1,subdescription1,price2,' +
           'subdescription2,price3,subdescription3,price,HappyHourPrice1,HappyHourPrice2,' +
           'HappyHourPrice3,HappyHourPrice4,WeekendPrice,WeekendPrice1,'+
           'WeekendPrice2,WeekendPrice3,Instruction,TaxRate ' +
           ' from menuitem where Active = 1 and Instruction <> 1 order by itemcode';
    result := OpenQuery(sql);
  end;

 function TDataModule1.GetProfile: TSQLQuery;
 var sql : string;
 begin
    SQLQuery.SQLConnection := GetConnection;
    sql := 'select ForceVIPDiscount,NotAllowModify,RoundingFlag,ServiceChargeRate,' +
        'HappyHour,HappyHourStartTime,HappyHourEndTime,EnableWeekendPriceFunction,' +
        'WeekendPriceStartDay,WeekendPriceEndDay from profile';
    result := OpenQuery(sql);
 end;

function TDataModule1.GetMenuInstructionLink: TSQLQuery;
var sql : string;
begin
    SQLQuery.SQLConnection := GetConnection;
    sql := 'select ml.*,mi.Description1 from SubMenuLinkDetail ml join Menuitem ' +
           'mi on ml.SubMenuCode = mi.ItemCode';
    result := OpenQuery(sql);
end;



 function TDataModule1.GetCategoryItems: TSQLQuery;
 var sql : string;
 begin
    SQLQuery.SQLConnection := GetConnection;
    sql := 'select * from category order by code';
    result := OpenQuery(sql);
 end;

 function TDataModule1.GetTablePages: TSQLQuery;
 var sql : string;
 begin
    SQLQuery.SQLConnection := GetConnection;
    sql := 'select * from TablePage';
    result := OpenQuery(sql);
 end;

 function TDataModule1.GetVIPTable: TSQLQuery;
 var sql : string;
 begin


    SQLQuery.SQLConnection := GetConnection;
    sql := 'select * from VIPTable';
    result := OpenQuery(sql);
 end;

 function TDataModule1.GetTableLayout: TSQLQuery;
 var sql : string;
 begin
    SQLQuery.SQLConnection := GetConnection;
    sql := 'select TableSet.TableNo, Description, ButtonShape, ButtonWidth, ButtonHeight, ButtonX, ButtonY,' +
           'propertyFlag, FontName, FontSize, FontBold, FontItalic, FontUnderline, FontStrikeout,' +
           'pageFlag  FROM TableSet';
//    sql := 'SELECT TableSet.TableNo, Description, ButtonShape, ButtonWidth, ButtonHeight, ButtonX, ButtonY,' +
//           'PropertyFlag, FontName, FontSize, FontBold, FontItalic, FontUnderline, FontStrikeout, PageFlag as PageNo,' +
//           'isnull(OrderNo,'') as OrderNo,OrderDate,OrderTable.OrderPrinted FROM TableSet Left Join ' +
//           '(Select TableNo, OrderNo,OrderDate,OrderPrinted From OrderH ' +
//           'Where Credit=0 and (PriceIncludesGST <> 0) and ' +
//           '(Amount + ServiceCharge + Tips + Surcharge + OtherCharge - PaidAmount>=0) and ' +
//           'OrderDate>= convert(char(10),GetDate(),120) + '' '' + (SELECT TOP 1 convert(char(8),BeginTime,108) from Profile)' +
//           'Union All Select TableNo, OrderNo,OrderDate,OrderPrinted ' +
//           'From OrderH Where Credit=0 and (PriceIncludesGST = 0) and ' +
//           '(Amount + ServiceCharge + Tips + Surcharge + OtherCharge + GST - PaidAmount>=0) ' +
//           'and OrderDate>=convert(char(10),GetDate(),120) + '' '' + ' +
//           '(SELECT TOP 1 convert(char(8),BeginTime,108) from Profile)) As OrderTable ' +
//           'On TableSet.TableNo=OrderTable.TableNo Order By PageFlag,TableSet.TableNo';
    result := OpenQuery(sql);
 end;

 function TDataModule1.OpenQuery(sql:string) : TSQLQuery;
begin
   SQLQuery.SQL.Clear;
   SQLQuery.SQL.Add(sql);
   SQLQuery.Open;
   Result := SQLQuery;
end;


  function TDataModule1.GetConnection: TSQLConnection;
  begin
  {$IFDEF IOS}
    AlexConn.Params.Values['Database']:= GetHomePath + PathDelim + 'Documents' + PathDelim + 'TestIOS.db';
  {$ELSE}
    {$IFDEF MSSQL}
    AlexConn.DriverName := 'MSSQL';
    AlexConn.Params.Text := 'SchemaOverride=sa.dbo'+ #$D#$A +'DriverName=MSSQL'+
         #$D#$A+ 'HostName=JACK-PC'+#$D#$A +'DataBase=Hospitality' +#$D#$A +
         'User_Name=sa' +#$D#$A+'Password=65233663'+ #$D#$A +'BlobSize=-1'+ #$D#$A
         + 'ErrorResourceFile='+#$D#$A+'LocaleCode=0000'+#$D#$A+'IsolationLevel=ReadCommitted'
         + #$D#$A + 'OS Authentication=False'+ #$D#$A+'Prepare SQL=False' + #$D#$A +
         'ConnectTimeout=60' + #$D#$A + 'Mars_Connection=False' + #$D#$A;
    {$ELSE}
         AlexConn.DriverName := 'SQLITE';
         AlexConn.Params.Text := 'SchemaOverride=sa.dbo'+ #$D#$A +'DriverName=SQLITE'+
         #$D#$A+ 'HostName=ALEX-HP'+#$D#$A +'DataBase=C:\TestIOS.db' +#$D#$A +
         'BlobSize=-1'+ #$D#$A + 'ErrorResourceFile='+#$D#$A+'LocaleCode=0000'+#$D#$A+'IsolationLevel=ReadCommitted'
         + #$D#$A + 'OS Authentication=False'+ #$D#$A+'Prepare SQL=False' + #$D#$A +
         'ConnectTimeout=60' + #$D#$A + 'Mars_Connection=False' + #$D#$A;
     {$ENDIF}
  {$ENDIF}

   Result := AlexConn;
  end;

 end.
